package com.example.tb.service.Impl;

import cn.hutool.core.util.IdUtil;

import java.io.*;
import com.example.tb.dao.BiOpeningRecordDao;
import com.example.tb.entity.BiOpeningRecord;
import com.example.tb.service.BiOpeningRecordService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.*;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
@Service
public class BiOpeningRecordServiceImpl implements BiOpeningRecordService {

    private final String XLSX = ".xlsx";
    private final String XLS=".xls";
    private final String DOC=".doc";
    private final String DOCX=".docx";
    private String PreFileDir = "D:/dev/未读xls文件";
    private String targetFileDir = "D:/dev/已读xls文件";
    private String notTargetFileDir = "D:/dev/无法读取文件";

    HashMap<String, String> map = new HashMap<>();
//        map.put("str1", "");
//        map.put("str2", "");
//        map.put("str3", "");
//        map.put("str4", "");

    @Autowired
    private BiOpeningRecordDao biOpeningRecordDao;

    /**
     * 获取Excel文件（.xls和.xlsx都支持）
     * @param file
     * @return  解析excle后的Json数据
     */
    public ArrayList<BiOpeningRecord> readExcel(File file) {
        int res = checkFile(file);

        if (res == 0) {
            System.out.println("File not found");
        }else if (res == 1) {
            try {
                return readXLSX(file);
            } catch (InvalidFormatException | IOException e) {
                e.printStackTrace();
            }
        }else if (res == 2) {
            try {
                return readXLS(file);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else if (res == 3) {

            //TODO 读取doc文件方法
            try {
                return parsingDocFile(file);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else if (res == 4) {

            //TODO 读取docx文件方法
            try {
                return parsingDocXFile(file);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else{
            System.out.println(file.getName()  +  "=====暂不支持该文件格式");
            BiOpeningRecordServiceImpl File = new BiOpeningRecordServiceImpl();
            File.renameFixFile("无法读取" + file.getName() ,PreFileDir,notTargetFileDir,file.getName());
        }
        return new ArrayList<>();
    }
    /**
     * 判断File文件的类型
     * @param file 传入的文件
     * @return 0-文件为空，1-XLSX文件，2-XLS文件，3-DOC文件，4-DOCX文件，3-其他文件
     */
    public int checkFile(File file){
        if (file==null) {
            return 0;
        }
        String flieName = file.getName();
        if (flieName.endsWith(XLSX)) {
            return 1;
        }
        if (flieName.endsWith(XLS)) {
            return 2;
        }
        if (flieName.endsWith(DOC)) {
            return 3;
        }
        if (flieName.endsWith(DOCX)) {
            return 4;
        }
        return 5;
    }

    /**
     * 读取XLSX文件
     * @param file
     * @return
     * @throws IOException
     * @throws InvalidFormatException
     */
    public ArrayList<BiOpeningRecord> readXLSX(File file) throws InvalidFormatException, IOException{
        Workbook book = new XSSFWorkbook(file);
        Sheet sheet = book.getSheetAt(0);
        String filename = file.getName();
        if (filename.startsWith("BD")){
            return readBD(sheet, book, filename);
        }
        return read(sheet, book, filename);
    }

    /**
     * 读取XLS文件
     * @param file
     * @return
     * @throws IOException
     * @throws FileNotFoundException
     */
    public ArrayList<BiOpeningRecord> readXLS(File file) throws FileNotFoundException, IOException{
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
        Workbook book = new HSSFWorkbook(poifsFileSystem);
        Sheet sheet = book.getSheetAt(0);
        String filename = file.getName();
        if (filename.startsWith("BD")){
            return readBD(sheet, book, filename);
        }
        return read(sheet, book, filename);
    }

    /**
     * 解析开标记录数据
     * @param sheet 表格sheet对象
     * @param book 用于流关闭
     * @return
     * @throws IOException
     */
    public ArrayList<BiOpeningRecord> read(Sheet sheet,Workbook book,String filename) throws IOException{
        ArrayList<BiOpeningRecord> biOpeningRecordList = new ArrayList<>();
        Map<String, String> map = new HashMap<String, String>();
        BiOpeningRecord biOpeningRecord = new BiOpeningRecord();

        int rowStart = sheet.getFirstRowNum();	// 首行下标
        int rowEnd = sheet.getLastRowNum();	// 尾行下标
        // 如果首行与尾行相同，表明只有一行，直接返回空数组
        if (rowStart == rowEnd) {
            book.close();
            return new ArrayList<BiOpeningRecord>();
        }
        // 获取第一行JSON对象键
        Row firstRow = sheet.getRow(2);
        int cellStart = firstRow.getFirstCellNum();
        int cellEnd = firstRow.getLastCellNum() - 1;
        Map<Integer, String> keyMap = new HashMap<Integer, String>();
        for (int j = cellStart; j < cellEnd; j++) {
            keyMap.put(j,getValue(firstRow.getCell(j), rowStart, j, book, true));
        }

        String bidOpeningTime = null;
        String itemNum = null;
        Row eachRow1 = sheet.getRow(rowStart + 1 );
        if (eachRow1 != null) {
            int index = getValue(eachRow1.getCell(3), rowStart + 1, 3, book, false).indexOf("：");
            bidOpeningTime = getValue(eachRow1.getCell(3), rowStart + 1, 3, book, false).substring(index+1);

            int index1 = getValue(eachRow1.getCell(1), rowStart + 1, 1, book, false).indexOf("：");
            itemNum = getValue(eachRow1.getCell(1), rowStart + 1, 1, book, false).substring(index1+1);
        }

        int i1 = filename.indexOf("-")+1;
        int i2 = filename.indexOf(".");
        String substring = filename.substring(i1, i2);

        for(int i = rowStart+3; i <= rowEnd ; i++) {
            Row eachRow = sheet.getRow(i);
            StringBuffer sb = new StringBuffer();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getValue(eachRow.getCell(k), i, k, book, false);
                    sb.append(val);		// 所有数据添加到里面，用于判断该行是否为空
                    map.put(keyMap.get(k),val);
                }
                biOpeningRecord.setBidOpeningTime(bidOpeningTime);
                biOpeningRecord.setItemNum(itemNum);
                biOpeningRecord.setBid(IdUtil.simpleUUID());
                biOpeningRecord.setBidRate(map.get("投标价格"));
                biOpeningRecord.setDataSource(substring);
                biOpeningRecord.setDiscountRate(map.get("折扣率"));
                biOpeningRecord.setFbName(map.get("分标名称"));
                biOpeningRecord.setFbNum(map.get("分标编号"));
                biOpeningRecord.setProjectUnit(map.get("项目单位"));
                biOpeningRecord.setSubcontractName(map.get("分包名称"));
                biOpeningRecord.setTurn(map.get("轮次"));
                biOpeningRecord.setRemark(map.get("开标备注"));
                biOpeningRecord.setTendererName(map.get("投标人名称"));
                biOpeningRecord.setIsWin("0");
                biOpeningRecord.setLimitedPrice("限价");
            }
            if (sb.toString().length() > 0) {
                biOpeningRecordDao.insert(biOpeningRecord);
            }
        }
        book.close();
        System.out.println(filename + "解析完成");
        BiOpeningRecordServiceImpl File = new BiOpeningRecordServiceImpl();
        File.renameFixFile("解析完成" + filename ,PreFileDir,targetFileDir,filename);
        return biOpeningRecordList;
    }

    /**
     * 解析八达数据
     * @param sheet 表格sheet对象
     * @param book 用于流关闭
     * @return
     * @throws IOException
     */
    public ArrayList<BiOpeningRecord> readBD(Sheet sheet,Workbook book,String filename) throws IOException{
        ArrayList<BiOpeningRecord> biOpeningRecordList = new ArrayList<>();
        Map<String, String> map = new HashMap<String, String>();
        BiOpeningRecord biOpeningRecord = new BiOpeningRecord();

        int rowStart = sheet.getFirstRowNum();	// 首行下标
        int rowEnd = sheet.getLastRowNum();	// 尾行下标
        // 如果首行与尾行相同，表明只有一行，直接返回空数组
        if (rowStart == rowEnd) {
            book.close();
            return new ArrayList<BiOpeningRecord>();
        }
        // 获取第一行JSON对象键
        Row firstRow = sheet.getRow(0);
        int cellStart = firstRow.getFirstCellNum();
        int cellEnd = firstRow.getLastCellNum();
        Map<Integer, String> keyMap = new HashMap<Integer, String>();
        for (int j = cellStart; j < cellEnd; j++) {
            keyMap.put(j,getValue(firstRow.getCell(j), rowStart, j, book, true));
        }

        for(int i = rowStart+1; i <= rowEnd ; i++) {
            Row eachRow = sheet.getRow(i);
            StringBuffer sb = new StringBuffer();
            for (int k = cellStart; k < cellEnd; k++) {
                if (eachRow != null) {
                    String val = getValue(eachRow.getCell(k), i, k, book, false);
                    sb.append(val);		// 所有数据添加到里面，用于判断该行是否为空
                    map.put(keyMap.get(k),val);
                }
                biOpeningRecord.setBidOpeningTime(null);
                biOpeningRecord.setItemNum(map.get("采购项目编号"));
                biOpeningRecord.setBid(IdUtil.simpleUUID());
                biOpeningRecord.setBidRate(map.get("投标价格（万元）"));
                biOpeningRecord.setDataSource(map.get("采购项目名称"));
                biOpeningRecord.setDiscountRate(map.get("折扣率"));
                biOpeningRecord.setFbName(map.get("分标名称"));
                biOpeningRecord.setFbNum(map.get("分标编号"));
                biOpeningRecord.setProjectUnit(map.get("项目单位"));
                biOpeningRecord.setSubcontractName(map.get("分包名称"));
                biOpeningRecord.setTurn(map.get("轮次"));
                biOpeningRecord.setRemark(map.get("开标备注"));
                biOpeningRecord.setTendererName(map.get("投标人名称"));
                biOpeningRecord.setIsWin("0");
                biOpeningRecord.setLimitedPrice("限价");
            }
            if (sb.toString().length() > 0) {
                biOpeningRecordDao.insert(biOpeningRecord);
            }
        }
        book.close();
        System.out.println(filename + "解析完成");
        BiOpeningRecordServiceImpl File = new BiOpeningRecordServiceImpl();
        File.renameFixFile("解析完成" + filename ,PreFileDir,targetFileDir,filename);
        return biOpeningRecordList;
    }

    /**
     * 获取每个单元格的数据
     * @param cell 单元格对象
     * @param rowNum 第几行
     * @param index 该行第几个
     * @param book 主要用于关闭流
     * @param isKey 是否为键：true-是，false-不是。 如果解析Json键，值为空时报错；如果不是Json键，值为空不报错
     * @return
     * @throws IOException
     */
    public String getValue(Cell cell, int rowNum, int index, Workbook book, boolean isKey) throws IOException{

        // 空白或空
        if (cell == null || cell.getCellType()== CellType.BLANK ) {
            if (isKey) {
                book.close();
                throw new NullPointerException(String.format("the key on row %s index %s is null ", ++rowNum,++index));
            }else{
                return "";
            }
        }

        // 0. 数字 类型
        if (cell.getCellType() == CellType.NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                return df.format(date);
            }
            String val = cell.getNumericCellValue()+"";
            val = val.toUpperCase();
            if (val.contains("E")) {
                val = val.split("E")[0].replace(".", "");
            }
            return val;
        }

        // 1. String类型
        if (cell.getCellType() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length()==0) {
                if (book != null) {
                    book.close();
                }
                return "";
            }
            return val.trim();
        }

        // 2. 公式 CELL_TYPE_FORMULA
        if (cell.getCellType() == CellType.FORMULA) {
            return cell.getStringCellValue();
        }

        // 4. 布尔值 CELL_TYPE_BOOLEAN
        if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue()+"";
        }

        // 5.	错误 CELL_TYPE_ERROR
        return "";
    }



    //文件重命名
    public Boolean renameFixFile(String newFileName,String PreFileDir,String targetFileDirs,String fixFileName) {

        try {
            //组合原始文件的绝对路径
            File fixFile = new File(PreFileDir + "/" + fixFileName);
            File newFile = new File(PreFileDir + "/" + newFileName);
            //重命名操作
            Boolean flag = fixFile.renameTo(newFile);
            System.out.println(newFile.exists());

            if (flag) {
                //还需要将重命名的文件移动到指定的位置
                String TargetFileDir = targetFileDirs + "/" + newFile.getName();
                System.out.println(TargetFileDir);

                File fixFileDir = new File(newFile.getAbsolutePath());
                File targetFileDir = new File(TargetFileDir);

                if (targetFileDir.exists()) {
                    targetFileDir.delete();
                }

                Boolean rmoveFlag = fixFileDir.renameTo(targetFileDir);
                if (rmoveFlag) {
                    System.out.println("移动成功");
                    return true;
                } else {
                    System.out.println("移动失败");
                }

            } else {
                return false;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public ArrayList<String> traversalFile(File file){
        ArrayList<String> objects = new ArrayList<>();
        File[] files = file.listFiles();
        for (File file2: files) {
            if (file2.isDirectory()){
                traversalFile(file2);
            }
            objects.add(file2.toString());
        }
        return objects;
    }

    public ArrayList<BiOpeningRecord> parsingDocFile (File file) throws IOException {
                InputStream is = new FileInputStream(file);
                // 处理doc格式 即office2003版本
                POIFSFileSystem pfs = new POIFSFileSystem(is);
                HWPFDocument hwpf = new HWPFDocument(pfs);
                Range range = hwpf.getRange();//得到文档的读取范围
                TableIterator it = new TableIterator(range);
                // 迭代文档中的表格
                // 如果有多个表格只读取需要的一个 set是设置需要读取的第几个表格，total是文件中表格的总数
                int set = 1, total = 4;
                int num = set;
                int storeCol = 0;
                for (int i = 0; i < set - 1; i++) {
                    it.hasNext();
                    it.next();

                }
                while (it.hasNext()) {
                    storeCol++;
                    org.apache.poi.hwpf.usermodel.Table tb = (Table) it.next();
                    //迭代行，默认从0开始,可以依据需要设置i的值,改变起始行数，也可设置读取到那行，只需修改循环的判断条件即可
            for (int i = 1; i < tb.numRows(); i++) {
                ArrayList<String> strings = new ArrayList<>();
                TableRow tr = tb.getRow(i);
                //迭代列，默认从0开始
                for (int j = 0; j < 4; j++) {


                    TableCell td = tr.getCell(j);//取得单元格

                    //取得单元格的内容
                    String text = "";
                    for (int k = 0; k < td.numParagraphs(); k++) {
                        Paragraph para = td.getParagraph(k);
                        String s = para.text();
                        //去除后面的特殊符号
                        if (null != s && !"".equals(s)) {
                            s = s.substring(0, s.length() - 1);
                        }
                        text += s;
                    }

                    HashMap<Object, Object> objectObjectHashMap = new HashMap<>();
                    objectObjectHashMap.put("var" + (j + storeCol), text);
                    map.put("str" + j, text);
                    System.out.println("------" + objectObjectHashMap);
                    strings.add(text);
                }
            }
                    // 过滤多余的表格
            while (num < total) {
                it.hasNext();
                it.next();
                num += 1;
            }
        }
        return null;
    }


    public ArrayList<BiOpeningRecord> parsingDocXFile (File file) throws IOException {
        InputStream is = new FileInputStream(file);
        XWPFDocument docx = new XWPFDocument(is);
        List<XWPFTable> tables = docx.getTables();
        //遍历word内的表格
        for (int i = 0; i < 1; i++) {
            XWPFTable table = tables.get(i);
            //读取每一行数据
            for (int j = 0; j < table.getNumberOfRows(); j++) {
                if (j >= 1) {
                    int storeCol = 0;//列合并单元格,不统计被合并的单元格,被合并的单元格 在此进行累计记录
                    HashMap<Object, Object> rowMap = new HashMap<>();
                    //读取表格的某一行的所有数据
                    XWPFTableRow row = table.getRow(j);
                    //读取每一列的数据
                    List<XWPFTableCell> cells = row.getTableCells();
                    String text = "";
                    for (int k = 0; k + storeCol < 4; k++) {
                        XWPFTableCell cell = cells.get(k);

                        List<XWPFParagraph> paragraphs1 = cell.getParagraphs();
                        text = "";
                        if (paragraphs1.size() <= 1) {
                            //一格一条数据
                            text = cell.getText();
                        } else if (paragraphs1.size() > 1) {
                            //一格多条数据
                            //表格内含有多个人的时候处理
                            text = "";
                            for (int h = 0; h < paragraphs1.size(); h++) {
                                XWPFParagraph xwpfParagraph = paragraphs1.get(h);
                                List<XWPFRun> runs = xwpfParagraph.getRuns();
                                if (runs.size() > 1) {
                                    for (int b = 0; b < runs.size(); b++) {
                                        XWPFRun xwpfRun = runs.get(b);
                                        text += xwpfRun.getText(0);
                                    }
                                } else {
                                    for (XWPFRun run : runs) {
                                        text += run.getText(0);
                                    }
                                }
                            }
                        }

                        CTTcPr tcPr = cell.getCTTc().getTcPr();
                        if (("").equals(text)) {
                            if (tcPr.isSetVMerge()) {
                                //行合并单元格
                                String s = map.get("str" + (k + storeCol));
                                text = s;
                            }
                        }

                        map.put("str" + (k + storeCol), text);
                        rowMap.put("var" + (k + storeCol), text);
                        if (tcPr.isSetGridSpan()) {
                            //列合并单元格
                            BigInteger val = tcPr.getGridSpan().getVal();//合并数
                            int colspan = val.intValue();

                            rowMap.put("colspan" + (k + storeCol), colspan);
                            int stop = k + colspan - 1;
                            for (int p = k + 1; p <= stop; p++) {
                                map.put("str" + (p + storeCol), text);
                                rowMap.put("var111111111" + (p + storeCol), text);
                                //   rowMap.put("colspan" + (p + storeCol), 0);
                            }
                            storeCol = storeCol + colspan - 1;
                        } else {
                            //   rowMap.put("colspan" + (k + storeCol), 1);
                        }

                    }

                    System.out.println(rowMap);
                }
            }
        }
        return null;
    }
}
